﻿Public NotInheritable Class PivotTable
    Private Sub New()
    End Sub
    ''' <summary>
    ''' Gets a Inverted DataTable
    ''' </summary>
    ''' <param name="table">DataTable do invert</param>
    ''' <param name="columnX">X Axis Column</param>
    ''' <param name="nullValue">null Value to Complete the Pivot Table</param>
    ''' <param name="columnsToIgnore">Columns that should be ignored in the pivot process (X Axis column is ignored by default)</param>
    ''' <returns>C# Pivot Table Method  - Felipe Sabino</returns>
    Public Shared Function GetInversedDataTable(ByVal table As DataTable, ByVal columnX As String, ByVal ParamArray columnsToIgnore As String()) As DataTable
        'Create a DataTable to Return
        Dim returnTable As New DataTable()

        If columnX = "" Then
            columnX = table.Columns(0).ColumnName
        End If

        'Add a Column at the beginning of the table
        returnTable.Columns.Add(columnX)

        'Read all DISTINCT values from columnX Column in the provided DataTale
        Dim columnXValues As New List(Of String)()


        'Creates list of columns to ignore
        Dim listColumnsToIgnore As New List(Of String)()
        If columnsToIgnore.Length > 0 Then
            listColumnsToIgnore.AddRange(columnsToIgnore)
        End If

        If Not listColumnsToIgnore.Contains(columnX) Then
            listColumnsToIgnore.Add(columnX)
        End If

        For Each dr As DataRow In table.Rows
            Dim columnXTemp As String = dr(columnX).ToString()
            'Verify if the value was already listed
            If Not columnXValues.Contains(columnXTemp) Then
                'if the value id different from others provided, add to the list of values and creates a new Column with its value.
                columnXValues.Add(columnXTemp)
                returnTable.Columns.Add(columnXTemp)
            Else
                'Throw exception for a repeated value
                Throw New Exception("The inversion used must have unique values for column " & columnX)
            End If
        Next

        'Add a line for each column of the DataTable
        For Each dc As DataColumn In table.Columns
            If Not columnXValues.Contains(dc.ColumnName) AndAlso Not listColumnsToIgnore.Contains(dc.ColumnName) Then
                Dim dr As DataRow = returnTable.NewRow()
                dr(0) = dc.ColumnName
                returnTable.Rows.Add(dr)
            End If
        Next

        'Complete the datatable with the values
        For i As Integer = 0 To returnTable.Rows.Count - 1
            For j As Integer = 1 To returnTable.Columns.Count - 1
                returnTable.Rows(i)(j) = table.Rows(j - 1)(returnTable.Rows(i)(0).ToString()).ToString()
            Next
        Next

        Return returnTable
    End Function

    ''' <summary>
    ''' Gets a Inverted DataTable
    ''' </summary>
    ''' <param name="table">Provided DataTable</param>
    ''' <param name="columnX">X Axis Column</param>
    ''' <param name="columnY">Y Axis Column</param>
    ''' <param name="columnZ">Z Axis Column (values)</param>
    ''' <param name="columnsToIgnore">Whether to ignore some column, it must be provided here</param>
    ''' <param name="nullValue">null Values to be filled</param>
    ''' <returns>C# Pivot Table Method  - Felipe Sabino</returns>
    Public Shared Function GetInversedDataTable(ByVal table As DataTable, ByVal columnX As String, ByVal columnY As String, ByVal columnZ As String, ByVal nullValue As String, ByVal sumValues As Boolean) As DataTable
        'Create a DataTable to Return
        Try
            Dim returnTable As New DataTable()
            If columnX = "" Then
                columnX = table.Columns(0).ColumnName
            End If
            'Add a Column at the beginning of the table
            returnTable.Columns.Add(columnY)
            'Read all DISTINCT values from columnX Column in the provided DataTale
            Dim columnXValues As New List(Of String)()
            For Each dr As DataRow In table.Rows
                Dim columnXTemp As String = dr(columnX).ToString()
                If Not columnXValues.Contains(columnXTemp) Then
                    'Read each row value, if it's different from others provided, add to the list of values and creates a new Column with its value.
                    columnXValues.Add(columnXTemp)
                    returnTable.Columns.Add(columnXTemp)
                    'returnTable.Columns.Add("")
                End If
            Next
            'Verify if Y and Z Axis columns re provided
            If columnY <> "" AndAlso columnZ <> "" Then
                'Read DISTINCT Values for Y Axis Column
                Dim columnYValues As New List(Of String)()
                For Each dr As DataRow In table.Rows
                    If Not columnYValues.Contains(dr(columnY).ToString()) Then
                        columnYValues.Add(dr(columnY).ToString())
                    End If
                Next
                'Loop all Column Y Distinct Value
                For Each columnYValue As String In columnYValues
                    'Creates a new Row
                    Dim drReturn As DataRow = returnTable.NewRow()
                    drReturn(0) = columnYValue
                    'foreach column Y value, The rows are selected distincted
                    Dim rows As DataRow() = table.[Select](columnY & "='" & columnYValue.Replace("'", "''") & "'")
                    'Read each row to fill the DataTable
                    For Each dr As DataRow In rows
                        Dim rowColumnTitle As String = dr(columnX).ToString()
                        'Read each column to fill the DataTable
                        For Each dc As DataColumn In returnTable.Columns
                            If dc.ColumnName = rowColumnTitle Then
                                'If Sum of Values is True it try to perform a Sum
                                'If sum is not possible due to value types, the value displayed is the last one read
                                If sumValues Then
                                    Try
                                        drReturn(rowColumnTitle) = Convert.ToDecimal(drReturn(rowColumnTitle)) + Convert.ToDecimal(dr(columnZ))
                                    Catch
                                        drReturn(rowColumnTitle) = dr(columnZ)
                                    End Try
                                Else
                                    drReturn(rowColumnTitle) = dr(columnZ)
                                End If
                            End If
                        Next
                    Next
                    returnTable.Rows.Add(drReturn)
                Next
            Else
                Throw New Exception("The columns to perform inversion are not provided")
            End If

            'if a nullValue is provided, fill the datable with it
            If nullValue <> "" Then
                For Each dr As DataRow In returnTable.Rows
                    For Each dc As DataColumn In returnTable.Columns
                        If dr(dc.ColumnName).ToString() = "" Then
                            dr(dc.ColumnName) = nullValue
                        End If
                    Next
                Next
            End If
            Return returnTable
        Catch ex As Exception
            Return Nothing
        End Try
    End Function
    
End Class